package com.codefactory.generator.utils;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelReader {

    /**
     * 读取excel
     *
     * @param filePath   文件路径
     * @param sheetIndex sheet索引，从0开始
     * @return
     */
    public static List<List<String>> readExcel(String filePath, int sheetIndex) throws Exception {
        List<List<String>> data = new ArrayList<>();

        try {
            FileInputStream file = new FileInputStream(filePath);

            // 创建一个工作簿对象
            Workbook workbook = WorkbookFactory.create(file);

            // 获取指定索引的工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);

            // 创建公式求值器
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter formatter = new DataFormatter();

            // 迭代行
            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();

                // 迭代列
                for (Cell cell : row) {
                    //int columnIndex = cell.getColumnIndex();
                    String cellValue = null;
                    if (cell.getCellType().name().equals(CellType.FORMULA.name())) {
                        cellValue = formatter.formatCellValue(cell, evaluator);
                    } else {
                        cellValue = formatter.formatCellValue(cell);
                    }

                    rowData.add(cellValue);
                }

                data.add(rowData);
            }

            workbook.close(); // 关闭工作簿

        } catch (Exception e) {
            throw e;
        }

        return data;
    }

//    public static void main(String[] args) throws Exception {
//        String filePath = "path/to/your/excel/file.xlsx";
//        int sheetIndex = 0; // 指定要读取的工作表索引
//
//        List<List<String>> excelData = ExcelReader.readExcel(filePath, sheetIndex);
//
//        // 输出读取的数据
//        for (List<String> rowData : excelData) {
//            for (Object cellData : rowData) {
//                System.out.print(cellData + "\t");
//            }
//            System.out.println(); // 换行
//        }
//    }
}
